from flask_records.decorators import query_by_page, query
from common.utils import custom_marshal_list
from app.product.category.dao import get_category_children_recursive
from common.utils.utils import custom_marshal_pagination

GET_BY_PAGE_SQL = """
select spu.id
from spu
         left join (select spu_id, sku_sn, barcode from sku) s on spu.id = s.spu_id
where (spu.is_active = :is_active or :is_active is null)
  and (
            lower(spu.name) like lower(:search_str_re) or
            lower(spu.short_name) like lower(:search_str_re) or
            lower(spu.spec_string) like lower(:search_str_re) or
            lower(spu.notes) like lower(:search_str_re) or
            lower(s.sku_sn) like lower(:search_str_re) or
            lower(s.barcode) like lower(:search_str_re) or
            :search_str_re is null
    )
  and (category_id in :category_ids_list or :category_ids_list is null)
group by spu.id
"""

GET_SPU_UNITS_SQL = """
select u.id, u.name, is_base_unit, num_of_base_unit
from spu_to_unit
         left join unit u on spu_to_unit.unit_id = u.id
where spu_id = :spu_id
"""

# 下载其实是下载商品， 名称使用 spu name + 属性1 + 属性2 ...
DOWNLOAD_SKU_SQL = """
select * from sku
    left join (select name from spu) s on sku.spu_id = s.id
"""


def get_by_page(page, page_size, sorted_by, sorted_by_type, search_str, category_id, **kwargs):
    sql = GET_BY_PAGE_SQL
    order_by_sql = "{} ORDER BY {} {}".format(sql, sorted_by, sorted_by_type)

    if search_str:
        search_str_re = '%{}%'.format(search_str)
    else:
        search_str_re = None

    if category_id:
        # 找到所有子类
        categories = custom_marshal_list(get_category_children_recursive(category_id))
        category_ids_list = [category['id'] for category in categories]
        category_ids_list = tuple(category_ids_list)
    else:
        category_ids_list = tuple([None])

    @query_by_page(order_by_sql, page_size=page_size)
    def _get_by_pagination(
            page, search_str_re, category_ids_list, is_active, is_delete):
        pass

    pagination = _get_by_pagination(
        page, search_str_re, category_ids_list, **kwargs
    )
    pagination = custom_marshal_pagination(pagination)
    return pagination


def download_skus(limit, offset):
    sql = "{} LIMIT {} OFFSET {}".format(DOWNLOAD_SKU_SQL, limit, offset)

    @query(sql)
    def _get():
        pass

    data = _get()
    data = custom_marshal_pagination(data)
    return data


def get_spu_to_units(spu_id):
    @query(GET_SPU_UNITS_SQL)
    def _get(spu_id):
        pass

    return custom_marshal_list(_get(spu_id))
